import mysql.connector
import datetime


class MySQL:
    def __init__(self, host, user, password, database):
        self.conn = mysql.connector.connect(
            host=host, user=user, password=password, database=database
        )
        self.cursor = self.conn.cursor()

    def execute(self, sql, values=None):
        if values:
            self.cursor.execute(sql, values)
        else:
            self.cursor.execute(sql)

    def fetchone(self):
        return self.cursor.fetchone()

    def fetchall(self):
        return self.cursor.fetchall()

    def query(self, sql, values=None):
        self.execute(sql, values)
        return self.fetchall()

    def insert(self, table, data):
        columns = ", ".join(data.keys())
        placeholders = ", ".join(["%s"] * len(data))
        sql = f"INSERT INTO {table} ({columns}) VALUES ({placeholders})"
        self.execute(sql, tuple(data.values()))
        self.conn.commit()

    def update(self, table, data, condition):
        set_values = ", ".join([f"{key} = '{value}'" for key, value in data.items()])
        sql = f"UPDATE {table} SET {set_values} WHERE {condition}"
        self.execute(sql)
        self.conn.commit()

    def delete(self, table, condition):
        sql = f"DELETE FROM {table} WHERE {condition}"
        self.execute(sql)
        self.conn.commit()

    def close(self):
        self.cursor.close()
        self.conn.close()


# 初始化MySQL类
db = MySQL("127.0.0.1", "haojg", "haojg", "stocks")

# 插入新记录
data = {
    "id": 0,
    "name": "test-del",
    "age": 30,
    "create_time": datetime.datetime.now(),
}
db.insert("t_test_table", data)


# 执行SELECT语句
db.execute("SELECT * FROM t_test_table")

# 获取一个结果
print(db.fetchone())

# 获取所有结果
print(db.fetchall())

# 插入新记录
data = {
    "id": 0,
    "name": "test-1",
    "age": 30,
    "create_time": datetime.datetime.now(),
}
db.insert("t_test_table", data)


# 更新记录
data = {"name": "test-2"}
condition = "id = 1"
db.update("t_test_table", data, condition)


# 删除记录
condition = "name ='test-del'"
db.delete("t_test_table", condition)

# 关闭连接
db.close()
